package com.qianfeng.shopping.dao;

import com.qianfeng.shopping.entity.Account;
import com.qianfeng.shopping.entity.Level;
import com.qianfeng.shopping.entity.Role;
import com.qianfeng.shopping.util.ConnDb;
import com.qianfeng.shopping.util.GetUuid;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

public class AccountDaoImpMap extends BaseDao implements AccountDao {
    @Override
    public Account login(Account acc) {
        String sql ="select \n" +
                "a.accountid,a.accountname,a.accountbirthday,a.accountphone,a.accountpwd,a.accountrealname,\n" +
                "a.levelid,a.roleid,r.roleid,r.rolename,r.rolememo,l.levelid,l.levelname,l.discount\n" +
                "from accounts a\n" +
                "left join role r\n" +
                "on a.roleid = r.roleid\n" +
                "left join levels l\n" +
                "on a.levelid = l.levelid\n"+
                "  where a.accountname=?   and a.accountpwd=?  and a.delflag=0 ";
        return select(sql,new Object[]{acc.getAccountName(),acc.getAccountPwd()});
//        Connection conn = ConnDb.getConnection();
//        PreparedStatement pst = null;
//        ResultSet rs = null;
//        String sql ="select \n" +
//                "a.accountid,a.accountname,a.accountbirthday,a.accountphone,a.accountpwd,a.accountrealname,\n" +
//                "a.levelid,a.roleid,r.roleid,r.rolename,r.rolememo,l.levelid,l.levelname,l.discount\n" +
//                "from accounts a\n" +
//                "left join role r\n" +
//                "on a.roleid = r.roleid\n" +
//                "left join levels l\n" +
//                "on a.levelid = l.levelid\n"+
//                "  where a.accountname=?   and a.accountpwd=?  and a.delflag=0 ";
//        try {
//            pst = conn.prepareStatement(sql);
//            pst.setString(1,acc.getAccountName());
//            pst.setString(2,acc.getAccountPwd());
//            rs = pst.executeQuery();
//            if(rs.next()){
//                Account account = new Account();
//                account.setAccountId(rs.getString("accountid"));
//                account.setAccountName(rs.getString("accountname"));
//                account.setAccountPwd(rs.getString("accountpwd"));
//                account.setAccountRealName(rs.getString("accountrealname"));
//                account.setAccountPhone(rs.getString("ACCOUNTPHONE"));
//                account.setAccountBirthday(rs.getString("accountbirthday"));
//                Role role = new Role();
//                role.setRoleId(rs.getInt("roleid"));
//                role.setRoleName(rs.getString("rolename"));
//                role.setRoleMemo(rs.getString("rolememo"));
//                Level level = new Level();
//                level.setLevelid(rs.getInt("levelid"));
//                level.setLevelname(rs.getString("levelname"));
//                level.setDiscount(rs.getDouble("discount"));
//                //  赋值到account中
//                account.setRole(role);
//                account.setLevel(level);
//                return account;
//            }
//        } catch (SQLException e) {
//            e.printStackTrace();
//        } finally {
//            ConnDb.closeDb(rs,pst,conn);
//        }
//        return null;
    }

    @Override
    public boolean register(Account account) {
        String sql ="insert into accounts(accountid,accountname,accountbirthday," +
                "accountphone,accountpwd,accountrealname,levelid,roleid) values  (" +
                "?,?,?,?,?,?,?,?)";
        return super.update(sql,new Object[]{GetUuid.getUuid(),account.getAccountName(),
                account.getAccountBirthday(),account.getAccountPhone(),
                account.getAccountPwd(),account.getAccountRealName(), 2,2});
//        Connection conn = ConnDb.getConnection();
//        PreparedStatement pst = null;
//        String sql ="insert into accounts(accountid,accountname,accountbirthday," +
//                "accountphone,accountpwd,accountrealname,levelid,roleid) values  (" +
//                "?,?,?,?,?,?,?,?)";
//        try {
//            pst = conn.prepareStatement(sql);
//            // uuid作为主键
//            pst.setString(1, GetUuid.getUuid());
//            pst.setString(2,account.getAccountName());
//            pst.setString(3,account.getAccountBirthday());
//            pst.setString(4,account.getAccountPhone());
//            pst.setString(5,account.getAccountPwd());
//            pst.setString(6,account.getAccountRealName());
//            // 设置默认注册的会员等级和角色等级
//            pst.setInt(7,2);
//            pst.setInt(8,2);
//            return pst.executeUpdate()==1;
//        } catch (SQLException e) {
//            e.printStackTrace();
//        } finally {
//            ConnDb.closeDb(null,pst,conn);
//        }
//        return false;
    }

    @Override
    public boolean checkAccountName(String accountName) {
        Connection conn = ConnDb.getConnection();
        PreparedStatement pst = null;
        ResultSet rs = null;
        String sql ="select  *  from accounts where  accountname=? ";
        try {
            pst = conn.prepareStatement(sql);
            pst.setString(1,accountName);
            rs = pst.executeQuery();
            if(rs.next()){
               return false;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ConnDb.closeDb(rs,pst,conn);
        }
        return true;
    }

    @Override
    public Map<String, Account> selectAll() {
        String sql ="select \n" +
                "a.accountid,a.accountname,a.accountbirthday,a.accountphone,a.accountpwd,a.accountrealname,\n" +
                "a.levelid,a.roleid,r.roleid,r.rolename,r.rolememo,l.levelid,l.levelname,l.discount\n" +
                "from accounts a\n" +
                "left join role r\n" +
                "on a.roleid = r.roleid\n" +
                "left join levels l\n" +
                "on a.levelid = l.levelid\n" +
                " where a.delflag=0";// 筛选有效数据
        return super.selectAll(sql,new Object[]{});
//        Map<String, Account> map = new HashMap<>();
//        Connection conn = ConnDb.getConnection();
//        PreparedStatement pst = null;
//        ResultSet rs = null;
//        String sql ="select \n" +
//                "a.accountid,a.accountname,a.accountbirthday,a.accountphone,a.accountpwd,a.accountrealname,\n" +
//                "a.levelid,a.roleid,r.roleid,r.rolename,r.rolememo,l.levelid,l.levelname,l.discount\n" +
//                "from accounts a\n" +
//                "left join role r\n" +
//                "on a.roleid = r.roleid\n" +
//                "left join levels l\n" +
//                "on a.levelid = l.levelid\n" +
//                " where a.delflag=0";// 筛选有效数据
//        try {
//            pst = conn.prepareStatement(sql);
//            rs = pst.executeQuery();
//            while(rs.next()){
//                Account account = new Account();
//                account.setAccountId(rs.getString("accountid"));
//                account.setAccountName(rs.getString("accountname"));
//                account.setAccountPwd(rs.getString("accountpwd"));
//                account.setAccountRealName(rs.getString("accountrealname"));
//                account.setAccountPhone(rs.getString("ACCOUNTPHONE"));
//                account.setAccountBirthday(rs.getString("accountbirthday"));
//                Role role = new Role();
//                role.setRoleId(rs.getInt("roleid"));
//                role.setRoleName(rs.getString("rolename"));
//                role.setRoleMemo(rs.getString("rolememo"));
//                Level level = new Level();
//                level.setLevelid(rs.getInt("levelid"));
//                level.setLevelname(rs.getString("levelname"));
//                level.setDiscount(rs.getDouble("discount"));
//                //  赋值到account中
//                account.setRole(role);
//                account.setLevel(level);
//                map.put(GetUuid.getUuid(),account);
//            }
//        } catch (SQLException e) {
//            e.printStackTrace();
//        } finally {
//            ConnDb.closeDb(rs,pst,conn);
//        }
//        return map;
    }

    @Override
    public Map<String, Account> select(String key) {
        Map<String, Account> map = new HashMap<>();
        Connection conn = ConnDb.getConnection();
        PreparedStatement pst = null;
        ResultSet rs = null;
        String sql ="select \n" +
                "a.accountid,a.accountname,a.accountbirthday,a.accountphone,a.accountpwd,a.accountrealname,\n" +
                "a.levelid,a.roleid,r.roleid,r.rolename,r.rolememo,l.levelid,l.levelname,l.discount\n" +
                "from accounts a\n" +
                "left join role r\n" +
                "on a.roleid = r.roleid\n" +
                "left join levels l\n" +
                "on a.levelid = l.levelid\n" +
                "  where a.accountname like ?";
        try {
            pst = conn.prepareStatement(sql);
            pst.setString(1,"%"+key+"%");
            rs = pst.executeQuery();
            while(rs.next()){
                Account account = new Account();
                account.setAccountId(rs.getString("accountid"));
                account.setAccountName(rs.getString("accountname"));
                account.setAccountPwd(rs.getString("accountpwd"));
                account.setAccountRealName(rs.getString("accountrealname"));
                account.setAccountPhone(rs.getString("ACCOUNTPHONE"));
                account.setAccountBirthday(rs.getString("accountbirthday"));
                Role role = new Role();
                role.setRoleId(rs.getInt("roleid"));
                role.setRoleName(rs.getString("rolename"));
                role.setRoleMemo(rs.getString("rolememo"));
                Level level = new Level();
                level.setLevelid(rs.getInt("levelid"));
                level.setLevelname(rs.getString("levelname"));
                level.setDiscount(rs.getDouble("discount"));
                //  赋值到account中
                account.setRole(role);
                account.setLevel(level);
                map.put(GetUuid.getUuid(),account);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ConnDb.closeDb(rs,pst,conn);
        }
        return map;
    }

    @Override
    public boolean checkAccountId(String id) {
        Connection conn = ConnDb.getConnection();
        PreparedStatement pst = null;
        ResultSet rs = null;
        String sql ="select  *  from accounts where accountid=? ";
        try {
            pst = conn.prepareStatement(sql);
            pst.setString(1,id);
            rs = pst.executeQuery();
            if(rs.next()){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ConnDb.closeDb(rs,pst,conn);
        }
        return false;
    }

    @Override
    public boolean delete(String id) {
        String sql ="update  accounts  set  delflag=1  where accountid=?" ;
        return super.update(sql,new Object[]{id});
//        Connection conn = ConnDb.getConnection();
//        PreparedStatement pst = null;
//        // 逻辑删除
//        String sql ="update  accounts  set  delflag=1  where accountid=?" ;
//        try {
//            pst = conn.prepareStatement(sql);
//            pst.setString(1, id);
//            return pst.executeUpdate()==1;
//        } catch (SQLException e) {
//            e.printStackTrace();
//        } finally {
//            ConnDb.closeDb(null,pst,conn);
//        }
//        return false;
    }

    @Override
    public boolean update(Account account) {
        String sql ="update  accounts  set  accountbirthday=?," +
                "accountphone=?,accountpwd=?,accountrealname=?  where accountid=?";
        return update(sql,new Object[]{
                account.getAccountBirthday(),account.getAccountPhone(),
                account.getAccountPwd(),account.getAccountRealName(),
                account.getAccountId()
        });
//        Connection conn = ConnDb.getConnection();
//        PreparedStatement pst = null;
//        String sql ="update  accounts  set  accountbirthday=?," +
//                "accountphone=?,accountpwd=?,accountrealname=?  where accountid=?";
//        try {
//            pst = conn.prepareStatement(sql);
//            pst.setString(1,account.getAccountBirthday());
//            pst.setString(2,account.getAccountPhone());
//            pst.setString(3,account.getAccountPwd());
//            pst.setString(4,account.getAccountRealName());
//            pst.setString(5, account.getAccountId());
//            return pst.executeUpdate()==1;
//        } catch (SQLException e) {
//            e.printStackTrace();
//        } finally {
//            ConnDb.closeDb(null,pst,conn);
//        }
//        return false;
    }

    @Override
    public int deleteBatch(String[] arr) {
        Connection conn = ConnDb.getConnection();
        PreparedStatement pst = null;
        String sql ="delete from accounts where accountid=?";
        try {
            //  设置手动提交
            //  默认是自动提交事务
            conn.setAutoCommit(false);
            pst = conn.prepareStatement(sql);
            for (int i = 0; i < arr.length; i++) {
                pst.setString(1,arr[i]);
                pst.addBatch();
            }
            int[] arrBatch =  pst.executeBatch();
            // 执行成功 影响行数1  否则0
            int row = 0;
            for (int i = 0; i <arr.length ; i++) {
                row+=arrBatch[i];
            }
            // 手动提交事务
            conn.commit();
            return row;
        } catch (SQLException e) {
            try {
                // 如果有异常,则回滚
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally {
            ConnDb.closeDb(null,pst,conn);
        }
        return 0;
    }

    @Override
    public Account selectAccount(String id) {
        String sql ="select \n" +
                "a.accountid,a.accountname,a.accountbirthday,a.accountphone,a.accountpwd,a.accountrealname,\n" +
                "a.levelid,a.roleid,r.roleid,r.rolename,r.rolememo,l.levelid,l.levelname,l.discount\n" +
                "from accounts a\n" +
                "left join role r\n" +
                "on a.roleid = r.roleid\n" +
                "left join levels l\n" +
                "on a.levelid = l.levelid\n"+
                "  where a.accountid=?  and a.delflag=0 ";
        return select(sql,new Object[]{id});
    }

    public static void main(String[] args) {
        AccountDao accountDao = new AccountDaoImpMap();
        Account acc = new Account();
        acc.setAccountName("allen");
        acc.setAccountPwd("123");
//        System.out.println(accountDao.login(acc));
//        System.out.println(accountDao.selectAll());
//        System.out.println(accountDao.delete("4"));
        System.out.println(accountDao.selectAccount("1"));
    }

    @Override
    public Account rowMapper(ResultSet rs) {
        try {
            Account account = new Account();
            account.setAccountId(rs.getString("accountid"));
            account.setAccountName(rs.getString("accountname"));
            account.setAccountPwd(rs.getString("accountpwd"));
            account.setAccountRealName(rs.getString("accountrealname"));
            account.setAccountPhone(rs.getString("ACCOUNTPHONE"));
            account.setAccountBirthday(rs.getString("accountbirthday"));
            Role role = new Role();
            role.setRoleId(rs.getInt("roleid"));
            role.setRoleName(rs.getString("rolename"));
            role.setRoleMemo(rs.getString("rolememo"));
            Level level = new Level();
            level.setLevelid(rs.getInt("levelid"));
            level.setLevelname(rs.getString("levelname"));
            level.setDiscount(rs.getDouble("discount"));
            //  赋值到account中
            account.setRole(role);
            account.setLevel(level);
            return account;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}
